#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
数据库迁移脚本：更新followups表结构
"""

import sqlite3
import os

def migrate_followups_table():
    """更新followups表结构，添加缺失的字段"""
    db_path = os.path.join(os.path.dirname(__file__), 'eqsystem.db')
    
    try:
        # 连接数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print("开始更新followups表结构...")
        
        # 检查表是否存在
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='followups'")
        if not cursor.fetchone():
            print("followups表不存在，跳过迁移")
            return False
            
        # 检查是否已经存在project_id列
        cursor.execute("PRAGMA table_info(followups)")
        columns = [column[1] for column in cursor.fetchall()]
        
        # 需要添加的字段
        fields_to_add = [
            ('project_id', 'INTEGER'),
            ('solution_support', 'BOOLEAN DEFAULT 0'),
            ('solution_support_user_id', 'INTEGER'),
            ('solution_support_time', 'DATETIME'),
            ('solution_confirmed', 'BOOLEAN DEFAULT 0'),
            ('reminder_sent', 'BOOLEAN DEFAULT 0'),
            ('reminder_time', 'DATETIME')
        ]
        
        # 添加缺失的字段
        for field_name, field_type in fields_to_add:
            if field_name not in columns:
                print(f"添加字段: {field_name}")
                cursor.execute(f"ALTER TABLE followups ADD COLUMN {field_name} {field_type}")
            else:
                print(f"字段已存在: {field_name}")
        
        # 提交更改
        conn.commit()
        print("followups表结构更新完成")
        
        # 验证更新结果
        cursor.execute("PRAGMA table_info(followups)")
        updated_columns = [column[1] for column in cursor.fetchall()]
        print("更新后的表结构:", updated_columns)
        
        return True
        
    except Exception as e:
        print(f"更新followups表结构时出错: {str(e)}")
        return False
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    migrate_followups_table()